﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using NIKE_Project.DAL;

namespace NIKE_Project.BLL
{
    public class SalesStatistics
    {
        //调用DBHelper类
        DBHelper db = new DBHelper();

        //定义DataSet全局变量
        public DataSet ds;
        public DataSet SelectSalesmanName()
        {
            //销售统计
            //tabControl 2\tabPage 7
            //DataGridView 2加载事件
            //导购员下拉框
            string Sql = string.Format("select SalesmanName from Salesan where Role='导购员'");
            ds = db.getDataSet(Sql);
            DataRow dr = ds.Tables[0].NewRow();
            dr[0] = "全部";
            ds.Tables["nike"].Rows.InsertAt(dr, 0);
            return ds;
        }

        public DataSet CalculateProfit(string BarCode)
        {
            DataSet ds = db.getDataSet(BarCode);
            //获取SalesID
            string SalesID = ds.Tables[0].Rows[0]["SalesID"].ToString();
            //编写计算单笔利润SQL语句  单笔利润 =（折后价-进货价）* 销售数量
            //根据SalesID获取单笔利润的和
            string Sales = string.Format(@"select sum((SalesDetail.AloneAmount-Goods.StorePrice)*SalesDetail.Quantity) 
                                             from SalesDetail,Goods 
                                             where SalesDetail.GoodsID=Goods.GoodsID and SalesID='{0}'", SalesID);
            DataSet ds1 = db.getDataSet(Sales);
            return ds1;
        }

        public string QueryCondition(DateTime startTime, DateTime endTime,string comb1, string comb2)
        {
            string SqlStr = "";
            if (comb1 != "全部" && comb2!= "全部")
            {
                //定义变量获取下拉框值 获取导购员ID
                string SalesanName = string.Format("select SalesmanID from Salesan where SalesmanName='{0}'", comb1);
                DataSet ds1 = db.getDataSet(SalesanName);
                string SalesmanID = ds1.Tables[0].Rows[0]["SalesmanID"].ToString();
                //按入库时间降序排序   编写SQL语句  根据导购员ID查询
                SqlStr = string.Format(@"select ReceiptsCode,SalesDate,Amount,Sn.SalesmanName,S.CashierID from Sales S,Salesan Sn
                                       where S.SalesmanID=Sn.SalesmanID and S.SalesmanID='{0}' and SalesDate between'{1}' and '{2}' 
                                       order by SalesDate desc", SalesmanID, startTime, endTime);
            }
            if (comb1 == "全部" && comb2!= "全部")
            {
                //按入库时间降序排序   编写SQL语句  根据交易时间查询并降序排序
                SqlStr = string.Format(@"select ReceiptsCode,SalesDate,Amount,Sn.SalesmanName,S.CashierID from Sales S,Salesan Sn
                                       where S.SalesmanID=Sn.SalesmanID and SalesDate between'{0}' and '{1}' 
                                       order by SalesDate desc;", startTime, endTime);
            }
            if (comb1 != "全部" && comb2== "全部")
            {
                //定义变量获取下拉框值  获取导购员ID
                string SalesanName = string.Format("select SalesmanID from Salesan where SalesmanName='{0}'", comb1);
                DataSet ds1 = db.getDataSet(SalesanName);
                string SalesmanID = ds1.Tables[0].Rows[0]["SalesmanID"].ToString();
                //按入库时间降序排序   编写SQL语句  根据导购员ID查询
                SqlStr = string.Format(@"select ReceiptsCode,SalesDate,Amount,Sn.SalesmanName,S.CashierID from Sales S,Salesan Sn
                                       where S.SalesmanID=Sn.SalesmanID and S.SalesmanID='{0}'
                                       order by SalesDate desc", SalesmanID);
            }
            if (comb1 == "全部" && comb2== "全部")
            {
                //按入库时间降序排序   编写SQL语句  根据导购员或交易时间查询
                SqlStr = string.Format(@"select ReceiptsCode,SalesDate,Amount,Sn.SalesmanName,S.CashierID from Sales S,Salesan Sn
                                       where S.SalesmanID=Sn.SalesmanID order by SalesDate desc");
            }

            return SqlStr;
        }

        public void ExportExcel(DataGridView dataGridView2)
        {
            string fileName = "";

            string saveFileName = "";

            SaveFileDialog saveDialog = new SaveFileDialog();

            saveDialog.DefaultExt = "xlsx";

            saveDialog.Filter = "Excel文件|*.xlsx";

            saveDialog.FileName = fileName;

            saveDialog.ShowDialog();

            saveFileName = saveDialog.FileName;

            if (saveFileName.IndexOf(":") < 0) return; //被点了取消

            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

            if (xlApp == null)

            {

                MessageBox.Show("无法创建Excel对象，您的程序可能未引用Excel");

                return;

            }

            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;

            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);

            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 

            //写入标题             

            for (int i = 0; i < dataGridView2.ColumnCount; i++)

            { worksheet.Cells[1, i + 1] = dataGridView2.Columns[i].HeaderText; }

            //写入数值

            for (int r = 0; r < dataGridView2.Rows.Count; r++)

            {
                for (int i = 0; i < dataGridView2.ColumnCount; i++)

                {

                    worksheet.Cells[r + 2, i + 1] = dataGridView2.Rows[r].Cells[i].Value;

                }

                System.Windows.Forms.Application.DoEvents();

            }

            worksheet.Columns.EntireColumn.AutoFit();//列宽自适应

            MessageBox.Show(fileName + "保存成功", "提示", MessageBoxButtons.OK);

            if (saveFileName != "")

            {

                try

                {
                    workbook.Saved = true;

                    workbook.SaveCopyAs(saveFileName);  //fileSaved = true;                 

                }

                catch (Exception ex)

                {//fileSaved = false;                      

                    MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);

                }

            }

            xlApp.Quit();

            GC.Collect();//强行销毁           
        }
    }
}
